DAX FILTER Function

The DAX (Data Analysis Expressions) FILTER function is used to return a table that contains only the rows that meet specific criteria. It evaluates a table expression in a context modified by filters, and it is commonly used to filter data based on conditions.

Syntax

FILTER(<table>, <filter_expression>)

table: The table or table expression to filter.

filter_expression: The condition or expression that specifies which rows to include in the filtered table.

Example with Tables

Consider the following table:

Sales Table

Suppose we have a "Sales" table with columns "Product", "SalesAmount", and "Year". We want to create a measure that calculates the total sales amount for the ProductId 101.

Steps to Create the Measure in Power BI Desktop

1. Open Power BI Desktop.

2. Load your data into the data model.

3. Go to the Data view by clicking on the table icon on the left pane.

4. Select the Sales table.

5. Click on "New Measure" in the Modeling tab.

6. Enter the DAX formula into the formula bar:

7. TotalSales_101 = CALCULATE( SUM(Sales[SalesAmount]), FILTER(Sales, Sales[Product_Id] = 101) )

8. Press Enter to create measure.

Result

When you use this measure in a visualization, it will display the total sales amount for the Product Id 101.

This demonstrates how the FILTER function can be used to dynamically filter data based on specific conditions, allowing for more targeted analysis and visualization in Power BI.